﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;

namespace _4DJackPot
{
    public class DbInitMSACCESS
    {
        public DbInitMSACCESS()
        {

        }

        public bool ExecInit()
        {
            if (!ExistTableCheck("bets"))
            {
                if (!bets_TBInit()) return false;
            }
            if (!ExistTableCheck("bet_winnings"))
            {
                if (!bet_winnings_TBInit()) return false;
            }
            if (!ExistTableCheck("companies"))
            {
                if (!companies_TBInit()) return false;
            }
            if (!ExistTableCheck("company_games"))
            {
                if (!company_games_TBInit()) return false;
            }
            if (!ExistTableCheck("game_split"))
            {
                if (!game_split_TBInit()) return false;
            }
            if (!ExistTableCheck("game_winning_numbers"))
            {
                if (!game_winning_numbers_TBInit()) return false;
            }
            if (!ExistTableCheck("games"))
            {
                if (!games_TBInit()) return false;
            }
            if (!ExistTableCheck("permissions"))
            {
                if (!permissions_TBInit()) return false;
            }
            if (!ExistTableCheck("prize_default_type_amount"))
            {
                if (!prize_default_type_amount_TBInit()) return false;
            }
            if (!ExistTableCheck("prize_default_types"))
            {
                if (!prize_default_types_TBInit()) return false;
            }
            if (!ExistTableCheck("prize_type_amount"))
            {
                if (!prize_type_amount_TBInit()) return false;
            }
            if (!ExistTableCheck("prize_types"))
            {
                if (!prize_types_TBInit()) return false;
            }
            if (!ExistTableCheck("prize_types_default_match"))
            {
                if (!prize_types_default_match_TBInit()) return false;
            }
            if (!ExistTableCheck("users"))
            {
                if (!users_TBInit()) return false;
            }
            if (!ExistTableCheck("zero"))
            {
                if (!zero_TBInit()) return false;
            }

            // Check split table data
            if (!checkSplit())
            {
                return false;
            }

            // Create games automatically
            if (!Company_CreateGame())
            {
                return false;
            }

            // Draw games automatically
            if (!Company_DrawGame())
            {
                return false;
            }


            return true;
        }

        // users
        public bool users_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE users (" +
                        "[user_id] VARCHAR NOT NULL, " +
                        "[password] VARCHAR, " +
                        "[name] VARCHAR, " +
                        "[permission_id] INTEGER, " +
                        "[registered_datetime] VARCHAR, " +
                        "CONSTRAINT [PK_users] PRIMARY KEY " +
                        "( " +
                            "[user_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // prize_types_default_match
        public bool prize_types_default_match_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE prize_types_default_match (" +
                        "[prize_type_id] INTEGER NOT NULL, " +
                        "[prize_default_type_id] INTEGER, " +
                        "CONSTRAINT [PK_prize_types_default_match] PRIMARY KEY " +
                        "( " +
                            "[prize_type_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // prize_type_amount
        public bool prize_types_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE prize_types (" +
                        "[prize_type_id]  AUTOINCREMENT, " +
                        "[prize_name] VARCHAR, " +
                        "[prize_number_count] INTEGER, " +
                        "CONSTRAINT [PK_prize_types] PRIMARY KEY " +
                        "( " +
                            "[prize_type_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();

                // insert some data
                Global.dBCmd.CommandText = "INSERT INTO prize_types(prize_type_id, prize_name, prize_number_count) VALUES(1, 'First Prize', 1); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO prize_types(prize_type_id, prize_name, prize_number_count) VALUES(2, 'Second Prize', 1); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO prize_types(prize_type_id, prize_name, prize_number_count) VALUES(3, 'Third Prize', 1); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO prize_types(prize_type_id, prize_name, prize_number_count) VALUES(4, 'Special Prize', 10); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO prize_types(prize_type_id, prize_name, prize_number_count) VALUES(5, 'Consolation Prize', 10); ";
                Global.dBCmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // prize_type_amount
        public bool prize_type_amount_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE prize_type_amount (" +
                        "[prize_type_id] INTEGER NOT NULL, " +
                        "[forecast] VARCHAR, " +
                        "[default_amount] INTEGER " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();

                // insert some data
                Global.dBCmd.CommandText = "INSERT INTO prize_type_amount(prize_type_id, forecast, default_amount) VALUES(1, 'Big', 2000000); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO prize_type_amount(prize_type_id, forecast, default_amount) VALUES(2, 'Big', 1500000); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO prize_type_amount(prize_type_id, forecast, default_amount) VALUES(3, 'Big', 1000000); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO prize_type_amount(prize_type_id, forecast, default_amount) VALUES(4, 'Big', 300000); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO prize_type_amount(prize_type_id, forecast, default_amount) VALUES(5, 'Big', 60000); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO prize_type_amount(prize_type_id, forecast, default_amount) VALUES(1, 'Small', 2500000); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO prize_type_amount(prize_type_id, forecast, default_amount) VALUES(2, 'Small', 2000000); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO prize_type_amount(prize_type_id, forecast, default_amount) VALUES(3, 'Small', 1500000); ";
                Global.dBCmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // prize_default_types
        public bool prize_default_types_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE prize_default_types (" +
                        "[prize_default_type_id]  AUTOINCREMENT, " +
                        "[prize_default_name] VARCHAR, " +
                        "CONSTRAINT [PK_prize_default_types] PRIMARY KEY " +
                        "( " +
                            "[prize_default_type_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // prize_default_type_amount
        public bool prize_default_type_amount_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE prize_default_type_amount (" +
                        "[prize_default_type_id] INTEGER NOT NULL, " +
                        "[default_forecast] VARCHAR, " +
                        "[default_amount] INTEGER, " +
                        "CONSTRAINT [PK_prize_default_type_amount] PRIMARY KEY " +
                        "( " +
                            "[prize_default_type_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // permissions
        public bool permissions_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE permissions (" +
                        "[permission_id]  AUTOINCREMENT, " +
                        "[permission_name] VARCHAR, " +
                        "CONSTRAINT [PK_permissions] PRIMARY KEY " +
                        "( " +
                            "[permission_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();

                Global.dBCmd.CommandText = "INSERT INTO permissions(permission_name) VALUES('User'); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO permissions(permission_name) VALUES('Company'); ";
                Global.dBCmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // games
        public bool games_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE games (" +
                        "[game_id]  AUTOINCREMENT, " +
                        "[created_datetime] VARCHAR, " +
                        "[draw_datetime] VARCHAR, " +
                        "CONSTRAINT [PK_games] PRIMARY KEY " +
                        "( " +
                            "[game_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // game_winning_numbers
        public bool game_winning_numbers_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE game_winning_numbers (" +
                        "[game_id] INTEGER NOT NULL, " +
                        "[prize_type_id] INTEGER, " +
                        "[order] INTEGER, " +
                        "[number] DOUBLE " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // game_split
        public bool game_split_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE game_split (" +
                        "[game_id] INTEGER NOT NULL, " +
                        "[ratio] DOUBLE, " +
                        "CONSTRAINT [PK_game_split] PRIMARY KEY " +
                        "( " +
                            "[game_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();

                Global.dBCmd.CommandText = "insert into game_split(game_id, ratio) values(0, 0);";
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // company_games
        public bool company_games_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE company_games (" +
                        "[company_id] INTEGER NOT NULL, " +
                        "[game_id] INTEGER, " +
                        "CONSTRAINT [PK_game_id] PRIMARY KEY " +
                        "( " +
                            "[game_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        // companies
        public bool companies_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE companies (" +
                        "[company_id]  AUTOINCREMENT, " +
                        "[company_name] VARCHAR, " +
                        "[company_logo_url] VARCHAR, " +
                        "CONSTRAINT [PK_companies] PRIMARY KEY " +
                        "( " +
                            "[company_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();


                Global.dBCmd.CommandText = "INSERT INTO companies(company_name, company_logo_url) VALUES('Magnum 4D', 'magnum'); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO companies(company_name, company_logo_url) VALUES('Da Ma Cai 1+3D', 'damacai'); ";
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO companies(company_name, company_logo_url) VALUES('Sports Toto 4D', 'toto'); ";
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // bets
        public bool bets_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE bets (" +
                        "[bet_id]  AUTOINCREMENT, " +
                        "[game_id] INTEGER, " +
                        "[user_id] VARCHAR, " +
                        "[bet_number] DOUBLE, " +
                        "[bet_amount] INTEGER, " +
                        "[bet_datetime] VARCHAR, " +
                        "[forecast] VARCHAR, " +
                        "[permitation] VARCHAR, " +
                        "CONSTRAINT [PK_bets] PRIMARY KEY " +
                        "( " +
                            "[bet_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // bet_winnings
        public bool bet_winnings_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE bet_winnings (" +
                        "[bet_id] INTEGER NOT NULL, " +
                        "[prize_type_id] INTEGER, " +
                        "[bet_winning_amount] DECIMAL(20,0), " +
                        "[receipt_datetime] VARCHAR, " +
                        "CONSTRAINT [PK_bet_winnings] PRIMARY KEY " +
                        "( " +
                            "[bet_id] " +
                        ") " +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        // users
        public bool zero_TBInit()
        {
            try
            {
                string createQueryString =
                    "CREATE TABLE zero (" +
                        "[a] VARCHAR" +
                    ") ";
                Global.dBCmd.CommandText = createQueryString;
                Global.dBCmd.ExecuteNonQuery();
                Global.dBCmd.CommandText = "INSERT INTO zero(a) VALUES('1'); ";
                Global.dBCmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "help", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        public bool ExistTableCheck(string tableNameString)
        {
            DataTable dt = Global.dBCon.GetSchema("tables");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string dbTableNameString = dt.Rows[i]["TABLE_NAME"].ToString();
                if (dbTableNameString == tableNameString)
                {
                    return true;
                }
            }

            return false;
        }

        public bool checkSplit()
        {
            string selectQueryString =
                "SELECT game_id from game_split where game_id = 0;";

            Global.dBCmd.CommandText = selectQueryString;
            Object obj = Global.dBCmd.ExecuteScalar();


            if (obj == null)
            {
                Global.dBCmd.CommandText = "insert into game_split(game_id, ratio) values(0, 0);";
                Global.dBCmd.ExecuteNonQuery();
            }

            return true;
        }

        // Create games automatically
        public bool Company_CreateGame()
        {
            /*
             * Get latest game's draw date
             */
            string selectQueryString =
                "SELECT TOP 1 draw_datetime FROM games ORDER BY draw_datetime desc";

            Global.dBCmd.CommandText = selectQueryString;
            Object obj = Global.dBCmd.ExecuteScalar();


            /*
             * Set game start date and end date
             */
            DateTime startDate;
            DateTime endDate = DateTime.Today;
            endDate = endDate.AddDays(Global.companyCreateGameFutureRange);
            if (obj == null)
            {
                startDate = DateTime.Today;
            }
            else
            {
                startDate = DateTime.Parse(obj.ToString());
            }

            /*
             * If we need to add game, add games.
             */
            ArrayList gameDates = new ArrayList();
            if (startDate.CompareTo(endDate) < 0)
            {
                // Search dates which to be added.
                while (startDate.CompareTo(endDate) <= 0)
                {
                    foreach (System.DayOfWeek dayOfWeek in Global.companyCreateGameDayOfWeek)
                    {
                        if (startDate.DayOfWeek == dayOfWeek)
                        {
                            gameDates.Add(startDate);
                            break;
                        }
                    }
                    // Add day
                    startDate = startDate.AddDays(1);
                }

                // Get companies
                ArrayList companiesID = new ArrayList();
                selectQueryString =
                    "SELECT * FROM companies";
                Global.dBCmd.CommandText = selectQueryString;
                OleDbDataReader reader = Global.dBCmd.ExecuteReader();
                while (reader.Read())
                {

                    companiesID.Add(Convert.ToInt32(reader[0].ToString()));
                }
                reader.Close();

                // Add to database.
                foreach (DateTime gameDate in gameDates)
                {
                    foreach (int companyID in companiesID)
                    {
                        try
                        {
                            // insert into games
                            String insertQueryString =
                                "INSERT INTO games(created_datetime, draw_datetime) VALUES(Now(), '" + gameDate.ToString("d") + "');";
                            Global.dBCmd.CommandText = insertQueryString;
                            Global.dBCmd.ExecuteNonQuery();

                            // insert into company_games
                            insertQueryString =
                                "INSERT INTO company_games(company_id, game_id) SELECT TOP 1 " + companyID.ToString() + ", game_id FROM games ORDER BY game_id DESC;";
                            Global.dBCmd.CommandText = insertQueryString;
                            Global.dBCmd.ExecuteNonQuery();
                        }
                        catch(Exception e)
                        {
                            MessageBox.Show(e.ToString());
                        }
                    }
                }
            }

            return true;
        }


        // Draw games automatically
        public bool Company_DrawGame()
        {
            /*
             * Get games to be drawn
             */
            string selectQueryString =
                "select game_id from games where (select count(*) from game_winning_numbers where game_winning_numbers.game_id = games.game_id) = 0 and CDate(draw_datetime) <= Date();";
            String insertQueryString;

            Global.dBCmd.CommandText = selectQueryString;
            OleDbDataReader reader = Global.dBCmd.ExecuteReader();
            ArrayList gamesID = new ArrayList();
            while (reader.Read())
            {
                gamesID.Add(Convert.ToInt32(reader[0].ToString()));
            }
            reader.Close();


            /*
             * If there is game which to be drawn, draw.
             */
            if (gamesID.Count > 0)
            {
                // 1. get rows from prize_types and prize_type_amount.
                selectQueryString =
                    "select prize_types.prize_type_id, prize_types.prize_name, prize_types.prize_number_count, prize_type_amount.forecast, prize_type_amount.default_amount from prize_types, prize_type_amount where prize_types.prize_type_id = prize_type_amount.prize_type_id;" ;

                Global.dBCmd.CommandText = selectQueryString;
                reader = Global.dBCmd.ExecuteReader();
                ArrayList prizeTypes = new ArrayList();
                while (reader.Read())
                {
                    PrizeTypes pt = new PrizeTypes();
                    pt.prizeTypeID = Convert.ToInt32(reader[0].ToString());
                    pt.prizeName = reader[1].ToString();
                    pt.prizeNumberCount = Convert.ToInt32(reader[2].ToString());
                    pt.forecast = reader[3].ToString();
                    pt.defaultAmount = Convert.ToInt32(reader[4].ToString());
                    prizeTypes.Add(pt);
                }
                reader.Close();

                // For each games, do step 2.
                System.Random r = new System.Random();
                foreach(int gameID in gamesID)
                {
                    // 2. create rows in game_winning_numbers.
                    ArrayList drawnPrizeNumbers = new ArrayList();
                    foreach (PrizeTypes pt in prizeTypes)
                    {
                        // skip if already drawn.
                        if (drawnPrizeNumbers.IndexOf(pt.prizeTypeID) != -1)
                        {
                            continue;
                        }

                        // create row in game_winning_numbers.
                        for(int i = 1; i <= pt.prizeNumberCount; i++)
                        {
                            // draw number
                            pt.drawnNumber = r.Next(0, 9999).ToString("0000");
                            //if (pt.prizeTypeID == 1)
                            //    pt.drawnNumber = "1234";

                            insertQueryString =
                                "INSERT INTO game_winning_numbers(game_id, prize_type_id, `order`, `number`) " +
                                "values(" + gameID + ", " + pt.prizeTypeID + ", " + i + ", " + pt.drawnNumber + ");";
                            Global.dBCmd.CommandText = insertQueryString;
                            Global.dBCmd.ExecuteNonQuery();
                        }

                        // add to drawnPrizeNumbers
                        drawnPrizeNumbers.Add(pt.prizeTypeID);
                    }
                }
                // 3. create rows in bet_winnings.
                insertQueryString = 
                    "insert into bet_winnings(bet_id, prize_type_id, bet_winning_amount) " +
                    "select bets.bet_id, prize_types.prize_type_id, Format(bets.bet_amount * prize_type_amount.default_amount * (select (100-sum(ratio))/100 from game_split where game_id in (0, bets.game_id)), 'General Number') as bet_winning_amount from bets, game_winning_numbers, prize_types, prize_type_amount " + 
                    "where " + 
                    "bets.game_id = game_winning_numbers.game_id and game_winning_numbers.prize_type_id = prize_types.prize_type_id and prize_types.prize_type_id = prize_type_amount.prize_type_id and " + 
                    "bets.forecast = prize_type_amount.forecast and bets.bet_number = game_winning_numbers.`number`;";
                Global.dBCmd.CommandText = insertQueryString;
                Global.dBCmd.ExecuteNonQuery();
            }

            return true;
        }
    }
}
